{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Team</th>\n",
       "      <th>City</th>\n",
       "      <th>Medal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th>Season</th>\n",
       "      <th>Sport</th>\n",
       "      <th>Event</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1896</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Summer</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Athletics</th>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Athina</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greece</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Athina</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>23.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Athletics Men's 1,500 metres</th>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greece</td>\n",
       "      <td>Athina</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     Age  Height  \\\n",
       "Year Season Sport     Event                                        \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  24.0     NaN   \n",
       "                      Athletics Men's 1,500 metres   NaN     NaN   \n",
       "                      Athletics Men's 1,500 metres  22.0     NaN   \n",
       "                      Athletics Men's 1,500 metres  23.0   154.0   \n",
       "                      Athletics Men's 1,500 metres  21.0     NaN   \n",
       "\n",
       "                                                             Team    City  \\\n",
       "Year Season Sport     Event                                                 \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  United States  Athina   \n",
       "                      Athletics Men's 1,500 metres         Greece  Athina   \n",
       "                      Athletics Men's 1,500 metres      Australia  Athina   \n",
       "                      Athletics Men's 1,500 metres        Germany  Athina   \n",
       "                      Athletics Men's 1,500 metres         Greece  Athina   \n",
       "\n",
       "                                                     Medal  \n",
       "Year Season Sport     Event                                 \n",
       "1896 Summer Athletics Athletics Men's 1,500 metres  Silver  \n",
       "                      Athletics Men's 1,500 metres     NaN  \n",
       "                      Athletics Men's 1,500 metres    Gold  \n",
       "                      Athletics Men's 1,500 metres     NaN  \n",
       "                      Athletics Men's 1,500 metres     NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = '../data/olympic_athlete_events.csv'\n",
    "\n",
    "df = pd.read_csv(filename,\n",
    "                index_col=['Year', 'Season', 'Sport', 'Event'],\n",
    "                usecols=['Age', 'Height', 'Team', 'Year', 'Season', 'City', 'Sport', 'Event', 'Medal'])\n",
    "df = df.sort_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "Events take place in either summer or winter Olympic games, but not in both. As a result, the \"Season\" level in our multi-index is often unnecessary. Remove the \"Season\" level, and then find (again) the height of the tallest tennis medalist between 1980 and 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "208.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.reset_index('Season')\n",
    "df.loc[(slice(1980,2020), 'Tennis'), 'Height'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "In which city were the greatest number of gold medals awarded from 1980 onward?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "City\n",
       "Beijing           671\n",
       "Rio de Janeiro    665\n",
       "Athina            664\n",
       "Sydney            663\n",
       "London            632\n",
       "Atlanta           608\n",
       "Barcelona         559\n",
       "Seoul             520\n",
       "Los Angeles       497\n",
       "Moskva            457\n",
       "Sochi             202\n",
       "Torino            176\n",
       "Vancouver         174\n",
       "Salt Lake City    162\n",
       "Nagano            145\n",
       "Lillehammer       110\n",
       "Albertville       104\n",
       "Calgary            87\n",
       "Sarajevo           74\n",
       "Lake Placid        72\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1980:].loc[lambda df_: df_['Medal'] == 'Gold', 'City'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "How many gold medals were received by the United States since 1980? (Use the index to select the values.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1257"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1980:].loc[lambda df_: (df_['Team'] == 'United States') & (df_['Medal'] == 'Gold'), 'City'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
